<?php

class Database {

    protected $_db_handle;
    protected $_result;
    protected $_query;
    protected $_table;
    protected $_describe = array();
    protected $_order_by;
    protected $_order;
    protected $_extra_conditions;
    protected $_h_one;
    protected $_h_many;
    protected $_h_many_to_many;
    protected $_page;
    protected $_limit;

    /** Connects to database * */
    function connect($address, $account, $pwd, $name) {
        $this->_db_handle = @mysql_connect($address, $account, $pwd);
        if ($this->_db_handle != 0) {
            if (mysql_select_db($name, $this->_db_handle)) {
                return 1;
            } else {
                return 0;
            }
        } else {
            return 0;
        }
    }

    /** Disconnects from database * */
    function disconnect() {
        if (@mysql_close($this->_db_handle) != 0) {
            return 1;
        } else {
            return 0;
        }
    }

    /** Select Query * */
    function where($field, $value) {
        $this->_extra_conditions .= '`' . $this->_model . '`.`' . $field . '` = \'' . mysql_real_escape_string($value) . '\' AND ';
    }

    function like($field, $value) {
        $this->_extra_conditions .= '`' . $this->_model . '`.`' . $field . '` LIKE \'%' . mysql_real_escape_string($value) . '%\' AND ';
    }

    function show_has_one() {
        $this->_h_one = true;
    }

    function show_has_many() {
        $this->_h_many = true;
    }

    function show_many_to_many() {
        $this->_h_many_to_many = true;
    }

    function set_limit($limit) {
        $this->_limit = $limit;
    }

    function set_page($page) {
        $this->_page = $page;
    }

    function order_by($order_by, $order = 'ASC') {
        $this->_order_by = $order_by;
        $this->_order = $order;
    }

    function search() {
        global $inflect;

        $from = '`' . $this->_table . '` as `' . $this->_model . '` ';
        $conditions = '\'1\'=\'1\' AND ';
        $conditions_child = '';
        $from_child = '';

        if ($this->_h_one && isset($this->has_one)) {
            foreach ($this->has_one as $alias => $model) {
                $table = strtolower($inflect->pluralize($model));
                $singular_alias = strtolower($alias);
                $from .= 'LEFT JOIN `' . $table . '` as `' . $alias . '` ';
                $from .= 'ON `' . $this->_model . '`.`' . $singular_alias . '_id` = `' . $alias . '`.`id`  ';
            }
        }

        if ($this->id) {
            $conditions .= '`' . $this->_model . '`.`id` = \'' . mysql_real_escape_string($this->id) . '\' AND ';
        }

        if ($this->_extra_conditions) {
            $conditions .= $this->_extra_conditions;
        }

        $conditions = substr($conditions, 0, -4);

        if (isset($this->_order_by)) {
            $conditions .= ' ORDER BY `' . $this->_model . '`.`' . $this->_order_by . '` ' . $this->_order;
        }

        if (isset($this->_page)) {
            $offset = ($this->_page - 1) * $this->_limit;
            $conditions .= ' LIMIT ' . $this->_limit . ' OFFSET ' . $offset;
        }

        $this->_query = 'SELECT * FROM ' . $from . ' WHERE ' . $conditions;
        $this->_result = mysql_query($this->_query, $this->_db_handle);
        $result = array();
        $table = array();
        $field = array();
        $temp_results = array();
        $num_of_fields = mysql_num_fields($this->_result);
        for ($i = 0; $i < $num_of_fields; ++$i) {
            array_push($table, mysql_field_table($this->_result, $i));
            array_push($field, mysql_field_name($this->_result, $i));
        }
        if (mysql_num_rows($this->_result) > 0) {
            while ($row = mysql_fetch_row($this->_result)) {
                for ($i = 0; $i < $num_of_fields; ++$i) {
                    $temp_results[$table[$i]][$field[$i]] = $row[$i];
                }

                if ($this->_h_many && isset($this->has_many)) {
                    foreach ($this->has_many as $alias_child => $model_child) {
                        $query_child = '';
                        $conditions_child = '';
                        $from_child = '';

                        $table_child = strtolower($inflect->pluralize($model_child));
                        $plural_alias_child = strtolower($inflect->pluralize($alias_child));
                        $singular_alias_child = strtolower($alias_child);

                        $from_child .= '`' . $table_child . '` as `' . $alias_child . '`';

                        $conditions_child .= '`' . $alias_child . '`.`' . strtolower($this->_model) . '_id` = \'' . $temp_results[$this->_model]['id'] . '\'';

                        $query_child = 'SELECT * FROM ' . $from_child . ' WHERE ' . $conditions_child;
                        $result_child = mysql_query($query_child, $this->_db_handle);

                        $table_child = array();
                        $field_child = array();
                        $temp_results_child = array();
                        $results_child = array();

                        if (mysql_num_rows($result_child) > 0) {
                            $num_of_fields_child = mysql_num_fields($result_child);
                            for ($j = 0; $j < $num_of_fields_child; ++$j) {
                                array_push($table_child, mysql_field_table($result_child, $j));
                                array_push($field_child, mysql_field_name($result_child, $j));
                            }

                            while ($row_child = mysql_fetch_row($result_child)) {
                                for ($j = 0; $j < $num_of_fields_child; ++$j) {
                                    $temp_results_child[$table_child[$j]][$field_child[$j]] = $row_child[$j];
                                }
                                array_push($results_child, $temp_results_child);
                            }
                        }

                        $temp_results[$alias_child] = $results_child;
                        mysql_free_result($result_child);
                    }
                }


                if ($this->_h_many_to_many && isset($this->has_many_to_many)) {
                    foreach ($this->has_many_to_many as $alias_child => $table_child) {
                        $query_child = '';
                        $conditions_child = '';
                        $from_child = '';

                        $table_child = strtolower($inflect->pluralize($table_child));
                        $plural_alias_child = strtolower($inflect->pluralize($alias_child));
                        $singular_alias_child = strtolower($alias_child);

                        $sort_tables = array($this->_table, $plural_alias_child);
                        sort($sort_tables);
                        $join_table = implode('_', $sort_tables);

                        $from_child .= '`' . $table_child . '` as `' . $alias_child . '`,';
                        $from_child .= '`' . $join_table . '`,';

                        $conditions_child .= '`' . $join_table . '`.`' . $singular_alias_child . '_id` = `' . $alias_child . '`.`id` AND ';
                        $conditions_child .= '`' . $join_table . '`.`' . strtolower($this->_model) . '_id` = \'' . $temp_results[$this->_model]['id'] . '\'';
                        $from_child = substr($from_child, 0, -1);

                        $query_child = 'SELECT * FROM ' . $from_child . ' WHERE ' . $conditions_child;
                        $result_child = mysql_query($query_child, $this->_db_handle);

                        $table_child = array();
                        $field_child = array();
                        $temp_results_child = array();
                        $results_child = array();

                        if (mysql_num_rows($result_child) > 0) {
                            $num_of_fields_child = mysql_num_fields($result_child);
                            for ($j = 0; $j < $num_of_fields_child; ++$j) {
                                array_push($table_child, mysql_field_table($result_child, $j));
                                array_push($field_child, mysql_field_name($result_child, $j));
                            }

                            while ($row_child = mysql_fetch_row($result_child)) {
                                for ($j = 0; $j < $num_of_fields_child; ++$j) {
                                    $temp_results_child[$table_child[$j]][$field_child[$j]] = $row_child[$j];
                                }
                                array_push($results_child, $temp_results_child);
                            }
                        }

                        $temp_results[$alias_child] = $results_child;
                        mysql_free_result($result_child);
                    }
                }

                array_push($result, $temp_results);
            }

            if (mysql_num_rows($this->_result) == 1 && $this->id != null) {
                mysql_free_result($this->_result);
                $this->clear();
                return($result[0]);
            } else {
                mysql_free_result($this->_result);
                $this->clear();
                return($result);
            }
        } else {
            mysql_free_result($this->_result);
            $this->clear();
            return $result;
        }
    }

    /** Custom SQL Query * */
    function custom($query) {
        global $inflect;

        $this->_result = mysql_query($query, $this->_db_handle);

        $result = array();
        $table = array();
        $field = array();
        $temp_results = array();

        if (substr_count(strtoupper($query), "SELECT") > 0) {
            if (mysql_num_rows($this->_result) > 0) {
                $num_of_fields = mysql_num_fields($this->_result);
                for ($i = 0; $i < $num_of_fields; ++$i) {
                    array_push($table, mysql_field_table($this->_result, $i));
                    array_push($field, mysql_field_name($this->_result, $i));
                }
                while ($row = mysql_fetch_row($this->_result)) {
                    for ($i = 0; $i < $num_of_fields; ++$i) {
                        $table[$i] = ucfirst($inflect->singularize($table[$i]));
                        $temp_results[$table[$i]][$field[$i]] = $row[$i];
                    }
                    array_push($result, $temp_results);
                }
            }
            mysql_free_result($this->_result);
        }
        $this->clear();
        return($result);
    }

    /** Describes a Table * */
    protected function _describe() {
        global $cache;

        $this->_describe = $cache->get('describe_' . $this->_table . '.cache');

        if (!$this->_describe) {
            $this->_describe = array();
            $query = 'DESCRIBE ' . $this->_table;
            $this->_result = mysql_query($query, $this->_db_handle);
            while ($row = mysql_fetch_row($this->_result)) {
                array_push($this->_describe, $row[0]);
            }

            mysql_free_result($this->_result);
            $cache->set('describe_' . $this->_table . '.cache', $this->_describe);
        }

        foreach ($this->_describe as $field) {
            $this->$field = null;
        }
    }

    /** Delete an Object * */
    function delete() {
        if ($this->id) {
            $query = 'DELETE FROM ' . $this->_table . ' WHERE `id`=\'' . mysql_real_escape_string($this->id) . '\'';
            $this->_result = mysql_query($query, $this->_db_handle);
            $this->clear();
            if ($this->_result == 0) {
                /** Error Generation * */
                return -1;
            }
        } else {
            /** Error Generation * */
            return -1;
        }
    }

    /** Saves an Object i.e. Updates/Inserts Query * */
    function save() {
        $query = '';
        if (isset($this->id)) {
            $updates = '';
            foreach ($this->_describe as $field) {
                if ($this->$field) {
                    $updates .= '`' . $field . '` = \'' . mysql_real_escape_string($this->$field) . '\',';
                }
            }

            $updates = substr($updates, 0, -1);

            $query = 'UPDATE ' . $this->_table . ' SET ' . $updates . ' WHERE `id`=\'' . mysql_real_escape_string($this->id) . '\'';
        } else {
            $fields = '';
            $values = '';
            foreach ($this->_describe as $field) {
                if ($this->$field) {
                    $fields .= '`' . $field . '`,';
                    $values .= '\'' . mysql_real_escape_string($this->$field) . '\',';
                }
            }
            $values = substr($values, 0, -1);
            $fields = substr($fields, 0, -1);

            $query = 'INSERT INTO ' . $this->_table . ' (' . $fields . ') VALUES (' . $values . ')';
        }
        $this->_result = mysql_query($query, $this->_db_handle);
        $this->clear();
        if ($this->_result == 0) {
            /** Error Generation * */
            return -1;
        }
    }

    /** Clear All Variables * */
    function clear() {
        foreach ($this->_describe as $field) {
            $this->$field = null;
        }

        $this->_order_by = null;
        $this->_extra_conditions = null;
        $this->_h_one = null;
        $this->_h_many = null;
        $this->_h_many_to_many = null;
        $this->_page = null;
        $this->_order = null;
    }

    /** Pagination Count * */
    function total_pages() {
        if ($this->_query && $this->_limit) {
            $pattern = '/SELECT (.*?) FROM (.*)LIMIT(.*)/i';
            $replacement = 'SELECT COUNT(*) FROM $2';
            $count_query = preg_replace($pattern, $replacement, $this->_query);
            $this->_result = mysql_query($count_query, $this->_db_handle);
            $count = mysql_fetch_row($this->_result);
            $total_pages = ceil($count[0] / $this->_limit);
            return $total_pages;
        } else {
            /* Error Generation Code Here */
            return -1;
        }
    }

    function num_rows() {
        return mysql_num_rows($this->_result);
    }

    function insert_id() {
        return mysql_insert_id($this->_db_handle);
    }

    function free_result() {
        mysql_free_result($this->_result);
    }

    /** Get error string * */
    function get_error() {
        return mysql_error($this->_db_handle);
    }

}